/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package alwv.database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.joda.time.LocalDate;

/**
 * 
 * @author Brad Hanlon
 */
public class Query {
    /*
     * These need to be declared as strings in order for the tablemodel to work,
     * since a connection is passed in seperately
     */
    public static final String GET_ALL_MEMBERS = "SELECT m.idnum, firstName, lastName, address, city, state, "
                    + "zip, zip4, phonenumber, email "
                    + "FROM Member m "
                    + "JOIN Associate a "
                    + "ON m.idNum = a.idNum";
    public static final String GET_ALL_NONMEMBERS = "SELECT m.idnum, fullname, address, city, state, "
                    + "zip, zip4, phonenumber, email "
                    + "FROM Nonmember m "
                    + "JOIN Associate a "
                    + "ON m.idNum = a.idNum";
    public static final String GET_ALL_VENDORS = "SELECT m.idnum, vendorname, address, city, state, "
                    + "zip, zip4, phonenumber, email "
                    + "FROM Vendor m "
                    + "JOIN Associate a "
                    + "ON m.idNum = a.idNum";
    public static final String GET_ALL_TRANSACTIONS = "SELECT * FROM TRANSACTIONS";
    public static final String GET_ALL_BUDGET = "SELECT chs.catname, chs.subname, s.lobbyingexpense, sbv.value "
                    + "FROM Category c "
                    + "JOIN CategoryHasSubcategory chs "
                    + "ON c.name = chs.catname "
                    + "JOIN subcategory s "
                    + "ON s.name = chs.subname "
                    + "JOIN subcategorybudgetvalue sbv "
                    + "ON chs.subname = sbv.subname "
                    + "AND sbv.budgetyear=" + LocalDate.now().getYear();
    public static final String GET_ALL_ACCOUNTS = "SELECT * FROM ACCOUNT";
    
    public static ArrayList<Integer> getAllMemberIDs(Connection conn){
        try{
            String query = "SELECT idNum FROM Member";
            ResultSet rs = conn.prepareCall(query).executeQuery();
            ArrayList<Integer> ids = new ArrayList<Integer>();
            while(rs.next()){
                ids.add(rs.getInt(1));
            }
            return ids;
        }
        catch (SQLException e){
            return new ArrayList<Integer>();
        }
    }
    
    public static ArrayList<Integer> getAllNonMemberIDs(Connection conn){
        try{
            String query = "SELECT idNum FROM Nonmember";
            ResultSet rs = conn.prepareCall(query).executeQuery();
            ArrayList<Integer> ids = new ArrayList<Integer>();
            while(rs.next()){
                ids.add(rs.getInt(1));
            }
            return ids;
        }
        catch (SQLException e){
            return new ArrayList<Integer>();
        }
    }
        
    public static ArrayList<Integer> getAllVendorIDs(Connection conn){
        try{
            String query = "SELECT idNum FROM Vendor";
            ResultSet rs = conn.prepareCall(query).executeQuery();
            ArrayList<Integer> ids = new ArrayList<Integer>();
            while(rs.next()){
                ids.add(rs.getInt(1));
            }
            return ids;
        }
        catch (SQLException e){
            return new ArrayList<Integer>();
        }
    }
        
        
    
    /* Query 1 */
    
    /**
     * Returns a result set with the following data.
     * 
     * String - Member first name
     * String - Member last name
     * String - Member address
     * String - Member city
     * String - Member state
     * Integer - Member zip
     * Integer - Member zip4
     * 
     * @param conn
     * @return 
     */
    public static ResultSet getAllMemberInfo(Connection conn){
        try{
            String query = 
                      "SELECT firstName, lastName, address, city, state, "
                    + "zip, zip4 "
                    + "FROM Member m "
                    + "JOIN Associate a "
                    + "ON m.idNum = a.idNum";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    public static String getMemberName(Connection conn, int memberID){
        try{
            String query = "SELECT firstName, lastName FROM Member WHERE idNum = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, memberID);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getString(1) + " " + rs.getString(2);
            }
            else
                return "";
        }
        catch (SQLException e){
            return "";
        }
    }
    
    public static String getNonMemberName(Connection conn, int memberID){
        try{
            String query = "SELECT fullName FROM Nonmember WHERE idNum = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, memberID);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getString(1);
            }
            else
                return "";
        }
        catch (SQLException e){
            return "";
        }
    }
    
    /**
     * Returns a ResultSet with the following data.
     * 
     * String - Full name
     * String - Address
     * String - City
     * String - State
     * Integer - Zip
     * Integer - Zip4
     * 
     * @param conn
     * @return 
     */
    public static ResultSet getAllNonmemberInfo(Connection conn){
        try{
            String query = 
                      "SELECT fullName, address, city, state, zip, zip4 "
                    + "FROM Nonmember n "
                    + "JOIN Associate a "
                    + "ON n.idNum = a.idNum";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * Returns a ResultSet with the following data.
     * 
     * String - Vendor name
     * String - Address
     * String - City
     * String - State
     * Integer - Zip
     * Integer - Zip4
     * 
     * @param conn
     * @return 
     */
    public static ResultSet getAllVendorInfo(Connection conn){
        try{
            String query = 
                      "SELECT vendorName, address, city, state, zip, zip4 "
                    + "FROM Vendor v "
                    + "JOIN Associate a "
                    + "ON v.idNum = a.idNum";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 2 */
    
    public static ResultSet getUnpaidDues(Connection conn){
        try{
            String query = 
                      "SELECT firstName, lastName "
                    + "FROM Member "
                    + "WHERE idNum "
                    + "NOT IN "
                    + "(SELECT m.idNum "
                    + "FROM transactions t "
                    + "JOIN Member m ON t.associateId = m.idNum "
                    + "WHERE t.Subcategory = 'Dues' "
                    + "AND t.paymentAmount = 60)";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 3 */
    
    public static ResultSet getLobbyingExpenseTransactions(Connection conn){
        try{
            String query = 
                      "SELECT * "
                    + "FROM SubCategory s "
                    + "JOIN transactions t "
                    + "ON t.subCategory = s.name "
                    + "WHERE s.lobbyingExpense > 0";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 4 */
    
    public static ResultSet getPaypalTransactions(Connection conn){
        try{
            String query = 
                      "SELECT * "
                    + "FROM transactions "
                    + "WHERE note = 'PayPal'";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 5 */
    
    public static ResultSet getVendorPayments(Connection conn, int year){
        try{
            String query = 
                      "SELECT vendorName "
                    + "FROM Vendor v "
                    + "JOIN transactions t "
                    + "ON v.idNum = t.associateId "
                    + "WHERE t.paymentAmount < 0 "
                    + "AND t.transDate > '01/01/" + year + "'";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 6 */
    
    public static ResultSet getMonthlyTotals(Connection conn,
            int month, int year){
        try{
            String query;
            if(month != 12){
                query = 
                          "SELECT * "
                        + "FROM Category c "
                        + "JOIN CategoryHasSubCategory chs "
                        + "ON c.name = chs.catName "
                        + "JOIN Subcategory s "
                        + "ON chs.subName = s.name "
                        + "JOIN transactions t "
                        + "ON t.subCategory = s.name "
                        + "WHERE t.transDate >= '" +month + "/01/" + year + "' "
                        + "AND t.transDate <= '" +(month+1)+ "/01/" +year+ "' ";
            }
            else{
                query = 
                          "SELECT * "
                        + "FROM Category c "
                        + "JOIN CategoryHasSubCategory chs "
                        + "ON c.name = chs.catName "
                        + "JOIN Subcategory s "
                        + "ON chs.subName = s.name "
                        + "JOIN transactions t "
                        + "ON t.subCategory = s.name "
                        + "WHERE t.transDate >= '" +month + "/01/" + year + "' "
                        + "AND t.transDate <= '01/01/" + (year+1) + "' ";
            }
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 7 */
    
    public static ResultSet getYearlyTotals(Connection conn, int year){
        try{
            String query = 
                      "SELECT * "
                    + "FROM Category c "
                    + "JOIN CategoryHasSubCategory chs "
                    + "ON c.name = chs.catName "
                    + "JOIN Subcategory s "
                    + "ON chs.subName = s.name "
                    + "JOIN transactions t "
                    + "ON t.subCategory = s.name "
                    + "WHERE t.transDate >= '01/01/" + year + "' "
                    + "AND t.transDate <= '01/01/" + (year+1) + "'";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 8 */
    
    public static ResultSet getCategoriesUnderBudget(Connection conn){
        try{
            String query = 
                      "SELECT * "
                    + "FROM Subcategory s "
                    + "WHERE s.projectedBudget > "
                    + "(SELECT SUM(t.paymentAmount) "
                    + "FROM transactions t "
                    + "WHERE t.subCategory = s.name "
                    + "AND t.paymentAmount < 0)";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 9 */
    
    public static ResultSet getAccountBalances(Connection conn){
        try{
            String query = 
                      "SELECT * "
                    + "FROM Account";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 10 */
    
    public static ResultSet getTransByCategorySubcategory(Connection conn, 
            int year){
        try{
            String query = 
                      "SELECT * "
                    + "FROM transactions t "
                    + "WHERE t.transDate > '07/01/" + year + "' "
                    + "AND t.transDate <= '06/30/" + (year+1) + "' "
                    + "ORDER BY category ASC, subCategory ASC, transdate ASC";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 11 */
    
    public static ResultSet getMemberInKindTransactions(Connection conn){
        try{
            String query = 
                      "SELECT * "
                    + "FROM transactions t "
                    + "JOIN Member m "
                    + "ON t.associateId = m.idNum "
                    + "WHERE t.action = 'InKind'";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 12 */
    
    public static ResultSet getMemberDeductibleTransactions(Connection conn, 
            String firstName, String lastName){
        try{
            String query = 
                      "SELECT * "
                    + "FROM transactions t "
                    + "JOIN Member m "
                    + "ON t.associateId = m.idNum "
                    + "WHERE t.isDeductible = 1 "
                    + "AND m.firstName = ? "
                    + "AND m.lastName = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, firstName);
            ps.setString(2, lastName);
            return ps.executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }

    public static ArrayList<Object> getMemberDeductibleTransactions(Connection conn, 
            int id, int year){
        try{
            String query = 
                      "SELECT TRANSDATE, DEPOSITAMOUNT, CATEGORY "
                    + "FROM transactions t "
                    + "JOIN Member m "
                    + "ON t.associateId = m.idNum "
                    + "WHERE t.isDeductible = 1 "
                    + "AND idNum = ? "
                    + "AND YEAR(TRANSDATE) = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, id);
            ps.setInt(2, year);
            ResultSet rs = ps.executeQuery();
            
            ArrayList<Object> information = new ArrayList<Object>();
            
            while(rs.next()){
                information.add(rs.getDate(1));
                information.add(rs.getDouble(2));
                information.add(rs.getString(3));
            }
            
            return information;
        }
        catch (SQLException e){
            return new ArrayList<Object>();
        }
    }
    
    public static double getLobbyingExpense(Connection conn, String subcategory){
        try{
            String query = "SELECT lobbyingExpense FROM subcategory WHERE name = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, subcategory);
            ResultSet rs = ps.executeQuery();
            
            if(rs.next()){
                if(rs.getDouble(1) > 1){
                    return rs.getDouble(1);
                }
                else{
                    return -1.0;
                }
            }
            else{
                return -1.0;
            }
            
        }
        catch (SQLException e){
            return -1.0;
        }
    }
    
    public static double categoryIncomeActualValueAsOfDate(Connection conn, String category, int year, Date d){
        try{
            String query = "SELECT SUM(DEPOSITAMOUNT) FROM transactions WHERE " +
                    "TRANSDATE >= '07/01/2011' AND TRANSDATE <= ? "
                    + "AND category = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            System.out.println(d.toString());
            ps.setDate(1, d);
            ps.setString(2, category);
            ResultSet rs = ps.executeQuery();
            
            if(rs.next())
                return rs.getDouble(1);
            else
                return 0.0;            
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double subcategoryIncomeActualValueAsOfDate(Connection conn, String category, int year, Date d){
        try{
            String query = "SELECT SUM(DEPOSITAMOUNT) FROM transactions WHERE " +
                    "TRANSDATE >= '7/1/2011' AND TRANSDATE <= ? "
                    + "AND subcategory = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setDate(1, d);
            ps.setString(2, category);
            ResultSet rs = ps.executeQuery();
            
            if(rs.next())
                return rs.getDouble(1);
            else
                return 0.0;            
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double categoryExpenseActualValueAsOfDate(Connection conn, String category, int year, Date d){
        try{
            String query = "SELECT SUM(PaymentAmount) FROM transactions WHERE " +
                    "TRANSDATE >= '07/01/2011' AND TRANSDATE <= ? "
                    + "AND category = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            System.out.println(d.toString());
            ps.setDate(1, d);
            ps.setString(2, category);
            ResultSet rs = ps.executeQuery();
            
            if(rs.next())
                return rs.getDouble(1);
            else
                return 0.0;            
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double subcategoryExpenseActualValueAsOfDate(Connection conn, String category, int year, Date d){
        try{
            String query = "SELECT SUM(PaymentAmount) FROM transactions WHERE " +
                    "TRANSDATE >= '7/1/2011' AND TRANSDATE <= ? "
                    + "AND subcategory = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setDate(1, d);
            ps.setString(2, category);
            ResultSet rs = ps.executeQuery();
            
            if(rs.next())
                return rs.getDouble(1);
            else
                return 0.0;            
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double getCategoryBudgetValue(Connection conn, String category, int year){
        try{
            String query = "SELECT value FROM CategoryBudgetValue WHERE catName = ? AND budgetYear = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, category);
            ps.setInt(2, year);
            ResultSet rs = ps.executeQuery();
            
            if(rs.next())
                return rs.getDouble(1);
            else
                return 0.0;            
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double getIncomeCategoriesTotalBudget(Connection conn, int year){
        try{
            String query = "SELECT SUM(value) "
                    + "FROM CategoryBudgetValue, SubcategoryBudgetValue, Category, CategoryHasSubcategory "
                    + "WHERE ";
            PreparedStatement ps = conn.prepareStatement(query);
            return 0.0;
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double getSubcategoryBudgetValue(Connection conn, String category, int year){
        try{
            String query = "SELECT value FROM SubcategoryBudgetValue WHERE subName = ? AND budgetYear = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, category);
            ps.setInt(2, year);
            ResultSet rs = ps.executeQuery();
            
            if(rs.next())
                return rs.getDouble(1);
            else
                return 0.0;            
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static ResultSet getNonmemberDeductibleTransactions(Connection conn,
            String nonmemberName){
        try{
            String query = 
                      "SELECT * "
                    + "FROM transactions t "
                    + "JOIN Nonmember n "
                    + "ON t.associateId = n.idNum " 
                    + "WHERE t.isDeductible = 1 "
                    + "AND n.fullName = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, nonmemberName);
            return ps.executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 13 */
    
    public static double getTotalDeductibleMember(Connection conn, 
            String firstName, String lastName){
        try{
            String query = 
                      "SELECT SUM(t.depositAmount) "
                    + "FROM transactions t "
                    + "JOIN Member m "
                    + "ON t.associateId = m.idNum "
                    + "WHERE t.isDeductible = 1 "
                    + "AND m.firstName = ? "
                    + "AND m.lastName = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, firstName);
            ps.setString(2, lastName);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getDouble(1);
            }
            else{
                return 0;
            }
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }
    
    public static double getTotalDeductibleNonmember(Connection conn, 
            String nonmemberName){
        try{
            String query = 
                      "SELECT SUM(t.depositAmount) "
                    + "FROM transactions t "
                    + "JOIN Nonmember n "
                    + "ON t.associateId = n.idNum "
                    + "WHERE t.isDeductible = 1 "
                    + "AND n.fullName = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, nonmemberName);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getDouble(1);
            }
            else{
                return 0;
            }
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }
    
    /* Query 14 */
    
    public static ResultSet getTransWithNote(Connection conn){
        try{
            String query = 
                      "SELECT * "
                    + "FROM transactions "
                    + "WHERE note <> '' "
                    + "OR note IS NOT null";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    /* Query 15 */
    
    public static ResultSet getTransBetweenDates(Connection conn, Date start, 
            Date end){
        try{
            String query = 
                      "SELECT * "
                    + "FROM transactions "
                    + "WHERE transDate "
                    + "BETWEEN ? AND ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setDate(1, start);
            ps.setDate(2, end);
            return ps.executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    public static ResultSet getSpecificTransaction(Connection conn, int idnum){
        try{
            String query = 
                      "SELECT * "
                    + "FROM transactions "
                    + "WHERE transid = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, idnum);
            return ps.executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    
    /* Query 16 */
    
    public static void setMemberEmail(Connection conn, int memberID, 
            String email){
        try{
            String query = 
                      "UPDATE associate "
                    + "SET email=? "
                    + "WHERE associate.idNum = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, email);
            ps.setInt(2, memberID);
            ps.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    public static void updateFullTransaction(Connection conn, int transid, 
            LocalDate transdate, String category, String subcategory, double payment,
            int checknumber, String action, int associateid, String note,
            double deposit, int isdeductable){
        try{
            
            Date d = new Date(transdate.getYear(), transdate.getMonthOfYear(), transdate.getDayOfMonth());
            String query = 
                      "UPDATE transactions "
                    + "SET transdate=?, "
                    + "category=?, "
                    + "subcategory=?, "
                    + "paymentamount=?, "
                    + "checknumber=?, "
                    + "action=?, "
                    + "associateid=?, "
                    + "note=?, "
                    + "depositamount=?, "
                    + "isdeductible=? "
                    + "WHERE transid = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setDate(1, d);
            ps.setString(2, category);
            ps.setString(3, subcategory);
            ps.setDouble(4, payment);
            ps.setInt(5, checknumber);
            ps.setString(6, action);
            ps.setInt(7, associateid);
            ps.setString(8, note);
            ps.setDouble(9, deposit);
            ps.setInt(10, isdeductable);
            ps.setInt(11, transid);
            ps.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
                           
        public static void updateMember(Connection conn, int id, 
            String firstName, String lastName, String address, String city,
            String state, int zip, int zip4, String phone,
            String email){
        try{
           //Update Associate 
           String query1 = 
                      "UPDATE Associate "
                    + "SET address=?, "
                    + "city=?, "
                    + "state=?, "
                    + "zip=?, "
                    + "zip4=?, "
                    + "phoneNumber=?, "
                    + "email=? "
                    + "WHERE idNum = ?";
            PreparedStatement ps1 = conn.prepareStatement(query1);
            ps1.setString(1, address);
            ps1.setString(2, city);
            ps1.setString(3, state);
            ps1.setInt(4, zip);
            ps1.setInt(5, zip4);
            ps1.setString(6, phone);
            ps1.setString(7, email);
            ps1.setInt(8, id);
            ps1.executeUpdate();
            
            
            //Update Member
           String query2 = 
                      "UPDATE Member "
                    + "SET firstName=?, "
                    + "lastName=? "
                    + "WHERE idNum = ?";
            PreparedStatement ps2 = conn.prepareStatement(query2);
            ps2.setString(1, firstName);
            ps2.setString(2, lastName);
            ps2.setInt(3, id);
            ps2.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
   
   public static void updateNonMember(Connection conn, int id, 
            String fullName, String address, String city,
            String state, int zip, int zip4, String phone,
            String email){
        try{
           //Update Associate 
           String query1 = 
                      "UPDATE Associate "
                    + "SET address=?, "
                    + "city=?, "
                    + "state=?, "
                    + "zip=?, "
                    + "zip4=?, "
                    + "phoneNumber=?, "
                    + "email=? "
                    + "WHERE idNum = ?";
            PreparedStatement ps1 = conn.prepareStatement(query1);
            ps1.setString(1, address);
            ps1.setString(2, city);
            ps1.setString(3, state);
            ps1.setInt(4, zip);
            ps1.setInt(5, zip4);
            ps1.setString(6, phone);
            ps1.setString(7, email);
            ps1.setInt(8, id);
            ps1.executeUpdate();
            
            
            //Update NonMember
           String query2 = 
                      "UPDATE Nonmember "
                    + "SET fullName=? "
                    + "WHERE idNum = ?";
            PreparedStatement ps2 = conn.prepareStatement(query2);
            ps2.setString(1, fullName);
            ps2.setInt(2, id);
            ps2.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }      
            
   public static void updateVendor(Connection conn, int id, 
            String vendorName, String address, String city,
            String state, int zip, int zip4, String phone,
            String email){
        try{
           //Update Associate 
           String query1 = 
                      "UPDATE Associate "
                    + "SET address=?, "
                    + "city=?, "
                    + "state=?, "
                    + "zip=?, "
                    + "zip4=?, "
                    + "phoneNumber=?, "
                    + "email=? "
                    + "WHERE idNum = ?";
            PreparedStatement ps1 = conn.prepareStatement(query1);
            ps1.setString(1, address);
            ps1.setString(2, city);
            ps1.setString(3, state);
            ps1.setInt(4, zip);
            ps1.setInt(5, zip4);
            ps1.setString(6, phone);
            ps1.setString(7, email);
            ps1.setInt(8, id);
            ps1.executeUpdate();
            
            
            //Update Vendor
           String query2 = 
                      "UPDATE Vendor "
                    + "SET vendorName=? "
                    + "WHERE idNum = ?";
            PreparedStatement ps2 = conn.prepareStatement(query2);
            ps2.setString(1, vendorName);
            ps2.setInt(2, id);
            ps2.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    /* Query 17  */ 
    
    public static void addNewSubcategory(Connection conn, String category, 
            String subcategory, int projectedBudget, double lobbyingExpense){
        try{
            // First, insert into SubCategory
            String insert = "INSERT INTO SubCategory "
                    + "(name, lobbyingExpense, projectedBudget) "
                    + "VALUES (?,?,?)";
            PreparedStatement ps = conn.prepareStatement(insert);
            ps.setString(1, subcategory);
            ps.setDouble(2, lobbyingExpense);
            ps.setInt(3, projectedBudget);
            ps.executeUpdate();
            
            // Second, insert into CategoryHasSubCategory
            insert = "INSERT INTO CategoryHasSubCategory "
                    + "(catName, subName) "
                    + "VALUES "
                    + "(?,?)";
            ps = conn.prepareStatement(insert);
            ps.setString(1, category);
            ps.setString(2, subcategory);
            ps.executeUpdate();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    public static void addNewCategory(Connection conn, String category){
        try{
            // First, insert into SubCategory
            String insert = "INSERT INTO Category "
                    + "(name) "
                    + "VALUES (?)";
            PreparedStatement ps = conn.prepareStatement(insert);
            ps.setString(1, category);
            ps.executeUpdate();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    /* Query 18 */
    
    public static void addTransaction(Connection conn, LocalDate transDate, 
            String category, String subcategory, double paymentAmount, double depositAmount,
            int checkNumber, String action, int associateID, String note,
            int accountNum, int isdeductable){
        try{
            // First, insert into transactions
            String insert = "INSERT INTO transactions "
                    + "(transDate, category, subCategory, paymentAmount, depositAmount, "
                    + "checkNumber, action, associateId, note, "
                    + "isDeductible) "
                    + "VALUES (?, ?, ?, ?, "
                    + "?, ?, ?, ?, "
                    + "?, ?)";
            PreparedStatement ps = conn.prepareStatement(insert); 
            Date d = new Date(transDate.getYear(), transDate.getMonthOfYear(), transDate.getDayOfMonth());
            ps.setDate(1, d);
            ps.setString(2, category);
            ps.setString(3, subcategory);
            ps.setDouble(4, paymentAmount);
            ps.setDouble(5, depositAmount);
            ps.setInt(6,checkNumber);
            ps.setString(7, action);
            ps.setInt(8, associateID);
            ps.setString(9, note);
            ps.setInt(10, isdeductable);
            ps.executeUpdate();
            
            // Second, update account balance
            String update = "UPDATE Account "
                    + "SET balance=balance-?+? "
                    + "WHERE accountNum=?";
            ps = conn.prepareStatement(update);
            ps.setDouble(1, paymentAmount);
            ps.setDouble(2, depositAmount);
            ps.setInt(3, accountNum);
            ps.executeUpdate();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    /* Query 19 */
        
    /* Query 20 */
    
    /* Query 21 */
    
    /* Query 22 */
    
    public static void addMember(Connection conn, String firstName, 
            String lastName, String address, String city, String state, int zip,
            int zip4, String phoneNumber, String email){
        try{
            // First, insert into associate and getID
            int id = addAssociate(conn, address, city, state, zip, zip4, 
                    phoneNumber, email);
            // Second, insert into member
            String insert = "INSERT INTO Member (idNum, firstName, lastName) "
                    + "VALUES (?,?,?)";
            PreparedStatement ps = conn.prepareStatement(insert);
            ps.setInt(1, id);
            ps.setString(2, firstName);
            ps.setString(3, lastName);
            ps.executeUpdate();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    public static void addNonmember(Connection conn, String fullName, 
            String address, String city, String state, int zip,
            int zip4, String phoneNumber, String email){
        try{
            // First, insert into associate and getID
            int id = addAssociate(conn, address, city, state, zip, zip4, 
                    phoneNumber, email);
            // Second, insert into member
            String insert = "INSERT INTO Nonmember (idNum, fullName) "
                    + "VALUES (?,?)";
            PreparedStatement ps = conn.prepareStatement(insert);
            ps.setInt(1, id);
            ps.setString(2, fullName);
            ps.executeUpdate();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    public static void addVendor(Connection conn, String vendorName,
            String address, String city, String state, int zip,
            int zip4, String phoneNumber, String email){
        try{
            // First, insert into associate and getID
            int id = addAssociate(conn, address, city, state, zip, zip4, 
                    phoneNumber, email);
            // Second, insert into member
            String insert = "INSERT INTO Vendor (idNum, vendorName) "
                    + "VALUES (?,?)";
            PreparedStatement ps = conn.prepareStatement(insert);
            ps.setInt(1, id);
            ps.setString(2, vendorName);
            ps.executeUpdate();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    private static int addAssociate(Connection conn, String address, 
            String city, String state, int zip, int zip4, String phoneNumber, 
            String email){
        try{
            // First, insert into associate
            String insert = "INSERT INTO Associate "
                    + "(address, city, state, zip, zip4, phoneNumber, email) "
                    + "VALUES (?,?,?,?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(insert);
            ps.setString(1, address);
            ps.setString(2, city);
            ps.setString(3, state);
            ps.setInt(4, zip);
            ps.setInt(5, zip4);
            ps.setString(6, phoneNumber);
            ps.setString(7, email);
            ps.executeUpdate();
            
            // Second, return the associateID, should be the MAX val
            String query = "SELECT MAX(idNum) FROM Associate";
            ResultSet rs = conn.prepareCall(query).executeQuery();
            rs.next();
            return rs.getInt(1);
        }
        catch (SQLException e){
            e.printStackTrace();
        }
        return -1;
    }
    
    /* Query 23 */
    
    public static void updateTransactionAmount(Connection conn, int transID, 
            double amount, int accountNum){
        try{
            // First, get the initial amount
            String query = "SELECT depositAmount "
                    + "FROM Transactions "
                    + "WHERE transID = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, transID);
            ResultSet rs = ps.executeQuery();
            rs.next();
            double initAmount = rs.getDouble(1); 
            
            // Second, update transaction
            String update = "UPDATE Transactions "
                    + "SET depositAmount=? "
                    + "WHERE transID=?";
            ps = conn.prepareStatement(update);
            ps.setDouble(1, amount);
            ps.setInt(2, transID);
            ps.executeUpdate();
            
            // Third, update account balance
            update = "UPDATE Account "
                    + "SET balance=balance - ? + ?";
            ps = conn.prepareStatement(update);
            ps.setDouble(1, initAmount);
            ps.setDouble(2, amount);
            ps.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    /* Query 24 */
    
    public static void removeMember(Connection conn, int memberID){
        try{
            // First, remove from Member table
            String remove = "DELETE FROM Member "
                    + "WHERE idNum=?";
            PreparedStatement ps = conn.prepareStatement(remove);
            ps.setInt(1, memberID);
            ps.executeUpdate();
            
            // Second, remove from Associate table
            remove = "DELETE FROM Associate "
                    + "WHERE idNum=?";
            ps = conn.prepareStatement(remove);
            ps.setInt(1, memberID);
            ps.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    public static void removeTransaction(Connection conn, int transId) throws SQLException{
       
            String remove1 = "DELETE FROM adjusts "
                    + "WHERE transid=?";
            PreparedStatement ps1 = conn.prepareStatement(remove1);
            ps1.setInt(1, transId);
            ps1.executeUpdate();
        
        
            String remove = "DELETE FROM transactions "
                    + "WHERE transid=?";
            PreparedStatement ps = conn.prepareStatement(remove);
            ps.setInt(1, transId);
            ps.executeUpdate();
    }
    
    /* Query 25 */
    
    public static void setProjectedBudget(Connection conn, String subcategory,
            int amount, int year){
        try{
            String update = "UPDATE SubCategoryBudgetValue "
                    + "SET value=? "
                    + "WHERE subname=?"
                    + "AND budgetyear=?";
            PreparedStatement ps = conn.prepareStatement(update);
            ps.setString(2, subcategory);
            ps.setDouble(1, amount);
            ps.setInt(3, year);
            
            if(ps.executeUpdate() == 0) {
               String insert = "INSERT INTO SubcategoryBudgetValue (subname, budgetyear, value)"
                        + " VALUES (?,?,?)";
               PreparedStatement ps2 = conn.prepareStatement(insert);
               ps2.setString(1, subcategory);
               ps2.setInt(2,year);
               ps2.setInt(3, amount);
               ps2.executeUpdate();
               
                
            }
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    public static void setAccountBalance(Connection conn, double balance,
            int amount){
        try{
            String update = "UPDATE account "
                    + "SET balance=? "
                    + "WHERE accountnum=?";
            PreparedStatement ps = conn.prepareStatement(update);
            ps.setDouble(1, balance);
            ps.setInt(2, amount);
            ps.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    public static void setLobbying(Connection conn, String subcategory,
            double amount){
        try{
            String update = "UPDATE SubCategory "
                    + "SET lobbyingExpense=? "
                    + "WHERE name=?";
            PreparedStatement ps = conn.prepareStatement(update);
            ps.setString(2, subcategory);
            ps.setDouble(1, amount);
            ps.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    public static ResultSet getAllCategoryNames(Connection conn) {
        try{
            String query = "SELECT name FROM category";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    public static ResultSet getSubcategoriesUnderCategory(Connection conn, String category) {
        try{
            String query = "SELECT name FROM subcategory s"
                    + " JOIN categoryhassubcategory chs ON"
                    + " chs.subname = s.name"
                    + " WHERE chs.catname = '" + category + "'";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }        
        return null;
    }
    
    public static String getSubcategoryLobbyingPercent(Connection conn, String subcategory) {
        try{
            String query = "SELECT lobbyingexpense FROM subcategory WHERE name='" + subcategory + "'";
            ResultSet rs = conn.prepareCall(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY ).executeQuery();
            rs.absolute(1);
            return rs.getString("LOBBYINGEXPENSE");
        }   
        catch (SQLException e){
            e.printStackTrace();
            return "0";
        }        
    }
    
    public static String getSubcategoryProjection(Connection conn, String subcategory) {
        try{
            String query = "SELECT projectedbudget FROM subcategory WHERE name='" + subcategory + "'";
            ResultSet rs = conn.prepareCall(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).executeQuery();
            rs.absolute(1);
            return rs.getString("PROJECTEDBUDGET");
        }   
        catch (SQLException e){
            e.printStackTrace();
            return "0";
        }
    }

    public static ArrayList<Integer> getAllAssociateIds(Connection conn) {
        try{
            String query = "SELECT idNum FROM Associate";
            ResultSet rs = conn.prepareCall(query).executeQuery();
            ArrayList<Integer> ids = new ArrayList<Integer>();
            while(rs.next()){
                ids.add(rs.getInt(1));
            }
            return ids;
        }
        catch (SQLException e){
            return new ArrayList<Integer>();
        }
    }
    
    public static ArrayList<Integer> getAllTransactionIds(Connection conn) {
        try{
            String query = "SELECT transid FROM transactions";
            ResultSet rs = conn.prepareCall(query).executeQuery();
            ArrayList<Integer> ids = new ArrayList<Integer>();
            while(rs.next()){
                ids.add(rs.getInt(1));
            }
            return ids;
        }
        catch (SQLException e){
            return new ArrayList<Integer>();
        }
    }
    
    public static ResultSet getAllAccountNames(Connection conn){
        try{
            String query = 
                      "SELECT accountnum, name FROM account";
            return conn.prepareCall(query).executeQuery();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    
    
    public static ArrayList<String> getAllIncomeCategories(Connection conn){
        try{
            String query = "SELECT name FROM Category WHERE isIncome = 1";
            PreparedStatement ps = conn.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            ArrayList<String> cat = new ArrayList<String>();
            while(rs.next()){
                cat.add(rs.getString(1));
            }
            return cat;
        }
        catch (SQLException e){
            return null;
        }
    }
    
    public static ArrayList<String> getAllExpenseCategories(Connection conn){
        try{
            String query = "SELECT name FROM Category WHERE isIncome = 0";
            PreparedStatement ps = conn.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            ArrayList<String> cat = new ArrayList<String>();
            while(rs.next()){
                cat.add(rs.getString(1));
            }
            return cat;
        }
        catch (SQLException e){
            return null;
        }
    }
    
    public static ArrayList<Object> getIncomeTransactionsForYear(Connection conn, int year){
        try{
            String query = "SELECT MONTH(TRANSDATE), Category, SUM(DEPOSITAMOUNT) "
                    + "FROM Category, Transactions "
                    + "WHERE "
                    + "((YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 7 AND MONTH(TRANSDATE) <= 12)"
                    + "OR "
                    + "(YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 1 AND MONTH(TRANSDATE) <= 6))"
                    + "AND Transactions.Category = Category.name "
                    + "AND Category.isIncome = 1 "
                    + "GROUP BY "
                    + "Category, YEAR(TRANSDATE), MONTH(TRANSDATE)";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, year);
            ps.setInt(2, year+1);
            
            ResultSet rs = ps.executeQuery();
            ArrayList<FinancialRecord> records = new ArrayList<FinancialRecord>();
            
            while(rs.next()){
                FinancialRecord f = new FinancialRecord();
                f.month = rs.getInt(1);
                f.category = rs.getString(2);
                f.amount = rs.getDouble(3);
                records.add(f);
            }
                        
            ArrayList<Object> information = new ArrayList<Object>();
            ArrayList<String> categories = getAllIncomeCategories(conn);
            
            for(int i = 0; i < categories.size(); i++){
                String category = categories.get(i);
                ArrayList<FinancialRecord> recs = getAllRecords(records, category);
                boolean[] monthsWithValues = new boolean[12];
                for(int r = 0; r < recs.size(); r++){
                    FinancialRecord rec = recs.get(r);
                    int month = rec.month;
                    if(month >= 7 && month <= 12){
                        monthsWithValues[month-7] = true;
                    }
                    else{
                        monthsWithValues[month+5] = true;
                    }
                }
                ArrayList<FinancialRecord> gapsFilledIn = new ArrayList<FinancialRecord>();
                for(int r = 0; r < monthsWithValues.length; r++){
                    if(!monthsWithValues[r]){
                        FinancialRecord temp = new FinancialRecord();
                        temp.amount = 0.0;
                        temp.category = category;
                        if(r >= 0 && r <= 5){
                            temp.month = r + 7;
                        }
                        else{
                            temp.month = r - 5;
                        }
                        gapsFilledIn.add(temp);
                    }
                    else{
                        gapsFilledIn.add(recs.remove(0));
                    }
                }
                                
                for(int r = 0; r < gapsFilledIn.size(); r++){
                    if(r == 0){
                        information.add(gapsFilledIn.get(r).category);
                        information.add(gapsFilledIn.get(r).amount);
                    }
                    else{
                        information.add(gapsFilledIn.get(r).amount);
                    }
                }
                
                information.add(getIncomeCategoryTotalFiscalYear(conn, category, year));
            }
                       
            return information;
        }
        catch (SQLException e){
            return null;
        }
    }
    
    public static double getIncomeCategoryTotalFiscalYear(Connection conn, String category, int year){
        try{
            String query = "SELECT SUM(DEPOSITAMOUNT) "
                    + "FROM Transactions "
                    + "WHERE "
                    + "((YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 7 AND MONTH(TRANSDATE) <= 12)"
                    + "OR "
                    + "(YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 1 AND MONTH(TRANSDATE) <= 6))"
                    + "AND Transactions.Category = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, year);
            ps.setInt(2, year+1);
            ps.setString(3, category);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getDouble(1);
            }
            else{
                return 0.0;
            }
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double getExpenseCategoryTotalFiscalYear(Connection conn, String category, int year){
        try{
            String query = "SELECT SUM(PAYMENTAMOUNT) "
                    + "FROM Transactions "
                    + "WHERE "
                    + "((YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 7 AND MONTH(TRANSDATE) <= 12)"
                    + "OR "
                    + "(YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 1 AND MONTH(TRANSDATE) <= 6))"
                    + "AND Transactions.Category = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, year);
            ps.setInt(2, year+1);
            ps.setString(3, category);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getDouble(1);
            }
            else{
                return 0.0;
            }
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double getIncomeFiscalYearTotal(Connection conn, int year){
        try{
            String query = "SELECT SUM(DEPOSITAMOUNT) "
                    + "FROM Transactions "
                    + "WHERE "
                    + "((YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 7 AND MONTH(TRANSDATE) <= 12)"
                    + "OR "
                    + "(YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 1 AND MONTH(TRANSDATE) <= 6)) ";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, year);
            ps.setInt(2, year+1);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getDouble(1);
            }
            else{
                return 0.0;
            }
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double getExpenseFiscalYearTotal(Connection conn, int year){
        try{
            String query = "SELECT SUM(PAYMENTAMOUNT) "
                    + "FROM Transactions "
                    + "WHERE "
                    + "((YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 7 AND MONTH(TRANSDATE) <= 12)"
                    + "OR "
                    + "(YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 1 AND MONTH(TRANSDATE) <= 6))";;
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, year);
            ps.setInt(2, year+1);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getDouble(1);
            }
            else{
                return 0.0;
            }
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    private static ArrayList<FinancialRecord> getAllRecords(ArrayList<FinancialRecord> array, String category){
        ArrayList<FinancialRecord> records = new ArrayList<FinancialRecord>();
        
        for(int i = 0; i < array.size(); i++){
            FinancialRecord r = array.get(i);
            if(r.category.equals(category)){
                records.add(r);
            }
        }
        
        return records;
    }
    
    public static double getMonthlyIncomeTotal(Connection conn, int month, int year){
        try{
            String query = "SELECT SUM(DEPOSITAMOUNT) FROM Transactions WHERE MONTH(TRANSDATE) = ? AND YEAR(TRANSDATE) = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, month);
            ps.setInt(2, year);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getDouble(1);
            }
            else{
                return 0.0;
            }
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double getEducationFundIncomeTransactionMonthlyTotal(Connection conn, int month, int year){
        try{
            String query = "SELECT SUM(DEPOSITAMOUNT) FROM Transactions WHERE MONTH(TRANSDATE) = ? AND YEAR(TRANSDATE) = ? AND ACTION = 'Education Fund'";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, month);
            ps.setInt(2, year);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getDouble(1);
            }
            else{
                return 0.0;
            }
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double getEducationFundIncomeTransactionTotalFiscalYear(Connection conn, int year){
        try{
            String query = "SELECT SUM(DEPOSITAMOUNT) "
                    + "FROM Transactions "
                    + "WHERE "
                    + "((YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 7 AND MONTH(TRANSDATE) <= 12)"
                    + "OR "
                    + "(YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 1 AND MONTH(TRANSDATE) <= 6))"
                    + "AND ACTION = 'Education Fund'";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, year);
            ps.setInt(2, year+1);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getDouble(1);
            }
            else{
                return 0.0;
            }
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static double getMonthlyExpenseTotal(Connection conn, int month, int year){
        try{
            String query = "SELECT SUM(PAYMENTAMOUNT) FROM Transactions WHERE MONTH(TRANSDATE) = ? AND YEAR(TRANSDATE) = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, month);
            ps.setInt(2, year);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getDouble(1);
            }
            else{
                return 0.0;
            }
        }
        catch (SQLException e){
            return 0.0;
        }
    }
    
    public static ArrayList<Object> getExpenseTransactionsForYear(Connection conn, int year){
        try{
            String query = "SELECT MONTH(TRANSDATE), Category, SUM(PAYMENTAMOUNT) "
                    + "FROM Category, Transactions "
                    + "WHERE "
                    + "((YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 7 AND MONTH(TRANSDATE) <= 12)"
                    + "OR "
                    + "(YEAR(TRANSDATE) = ? "
                    + "AND MONTH(TRANSDATE) >= 1 AND MONTH(TRANSDATE) <= 6))"
                    + "AND Transactions.Category = Category.name "
                    + "AND Category.isIncome = 0 "
                    + "GROUP BY "
                    + "YEAR(TRANSDATE), MONTH(TRANSDATE), Category";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, year);
            ps.setInt(2, year+1);
            ResultSet rs = ps.executeQuery();
            
            ArrayList<FinancialRecord> records = new ArrayList<FinancialRecord>();
            
            while(rs.next()){
                FinancialRecord f = new FinancialRecord();
                f.month = rs.getInt(1);
                f.category = rs.getString(2);
                f.amount = rs.getDouble(3);
                records.add(f);
            }
                        
            ArrayList<Object> information = new ArrayList<Object>();
            ArrayList<String> categories = getAllExpenseCategories(conn);
            
            for(int i = 0; i < categories.size(); i++){
                String category = categories.get(i);
                ArrayList<FinancialRecord> recs = getAllRecords(records, category);
                boolean[] monthsWithValues = new boolean[12];
                for(int r = 0; r < recs.size(); r++){
                    FinancialRecord rec = recs.get(r);
                    int month = rec.month;
                    if(month >= 7 && month <= 12){
                        monthsWithValues[month-7] = true;
                    }
                    else{
                        monthsWithValues[month+5] = true;
                    }
                }
                ArrayList<FinancialRecord> gapsFilledIn = new ArrayList<FinancialRecord>();
                for(int r = 0; r < monthsWithValues.length; r++){
                    if(!monthsWithValues[r]){
                        FinancialRecord temp = new FinancialRecord();
                        temp.amount = 0.0;
                        temp.category = category;
                        if(r >= 0 && r <= 5){
                            temp.month = r + 7;
                        }
                        else{
                            temp.month = r - 5;
                        }
                        gapsFilledIn.add(temp);
                    }
                    else{
                        gapsFilledIn.add(recs.remove(0));
                    }
                }
                                
                for(int r = 0; r < gapsFilledIn.size(); r++){
                    if(r == 0){
                        information.add(gapsFilledIn.get(r).category);
                        information.add(gapsFilledIn.get(r).amount);
                    }
                    else{
                        information.add(gapsFilledIn.get(r).amount);
                    }
                }
                
                information.add(getExpenseCategoryTotalFiscalYear(conn, category, year));
            }
                      
            return information;
            
        }
        catch (SQLException e){
            return null;
        }
    }
    
    public static ArrayList<String> getAllIncomeCategoriesAndSubcategories(Connection conn){
        try{
            ArrayList<String> categories = getAllIncomeCategories(conn);
            ArrayList<String> categoriesAndSubcategories = new ArrayList<String>();
            for(int i = 0; i < categories.size(); i++){
                String category = categories.get(i);
                categoriesAndSubcategories.add(category);
                if(!hasSubcategory(conn, category)){
                    categoriesAndSubcategories.add("-");
                }
                else{
                    ResultSet rs = getSubcategoriesUnderCategory(conn, category);
                    while(rs.next()){
                        categoriesAndSubcategories.add(rs.getString(1));
                    }
                }
            }
            return categoriesAndSubcategories;
        }
        catch(SQLException e){
            return new ArrayList<String>();
        }
    }
    
    public static ArrayList<String> getAllExpenseCategoriesAndSubcategories(Connection conn){
        try{
            ArrayList<String> categories = getAllExpenseCategories(conn);
            ArrayList<String> categoriesAndSubcategories = new ArrayList<String>();
            for(int i = 0; i < categories.size(); i++){
                String category = categories.get(i);
                categoriesAndSubcategories.add(category);
                if(!hasSubcategory(conn, category)){
                    categoriesAndSubcategories.add("-");
                }
                else{
                    ResultSet rs = getSubcategoriesUnderCategory(conn, category);
                    while(rs.next()){
                        categoriesAndSubcategories.add(rs.getString(1));
                    }
                }
            }
            return categoriesAndSubcategories;
        }
        catch(SQLException e){
            return new ArrayList<String>();
        }
    }
    
    public static boolean isCategory(Connection conn, String category){
        try{
            String query = "SELECT name FROM Category WHERE name = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, category);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return true;
            }
            else
                return false;
        }
        catch (SQLException e){
            return false;
        }
    }
    
    public static boolean isSubcategory(Connection conn, String sub){
        try{
            String query = "SELECT name FROM Subcategory WHERE name = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, sub);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return true;
            }
            else
                return false;
        }
        catch (SQLException e){
            return false;
        }
    }
    
    public static boolean hasSubcategory(Connection conn, String category){
        try{
            String query = "SELECT subName FROM CategoryHasSubcategory WHERE catName = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, category);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return true;
            }
            else
                return false;
        }
        catch (SQLException e){
            return false;
        }
    }
    
    /* Query 26 REMOVE VENDOR */
    
    public static void removeVendor(Connection conn, int vendorID){
        try{
            // First, remove from Member table
            String remove = "DELETE FROM Vendor "
                    + "WHERE idNum=?";
            PreparedStatement ps = conn.prepareStatement(remove);
            ps.setInt(1, vendorID);
            ps.executeUpdate();
            
            // Second, remove from Associate table
            remove = "DELETE FROM Associate "
                    + "WHERE idNum=?";
            ps = conn.prepareStatement(remove);
            ps.setInt(1, vendorID);
            ps.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    
    /* Query 26 REMOVE NONMEMBERS */
    
    public static void removeNonMember (Connection conn, int nonMemberID){
        try{
            // First, remove from Member table
            String remove = "DELETE FROM Nonmember "
                    + "WHERE idNum=?";
            PreparedStatement ps = conn.prepareStatement(remove);
            ps.setInt(1, nonMemberID);
            ps.executeUpdate();
            
            // Second, remove from Associate table
            remove = "DELETE FROM Associate "
                    + "WHERE idNum=?";
            ps = conn.prepareStatement(remove);
            ps.setInt(1, nonMemberID);
            ps.executeUpdate();
        }   
        catch (SQLException e){
            e.printStackTrace();
        }
    }
 
}

class FinancialRecord {
    
    public int month;
    public String category;
    public double amount;
    
}